import pymysql
import xlwt


# 统计商品库存情况
def exe_db():
    # 打开数据库连接，不指定数据库
    conn = pymysql.connect(host='localhost', port=3306, user='root', password='xuhuan.5130638')
    # conn = pymysql.connect(host='139.155.88.162', port=3306, user='root', password='xuhuan.59')
    conn.select_db('linjiashop_backups')
    cur = conn.cursor()  # 获取游标

    # ================
    # 数据记录
    name_list = []
    num_list = []

    try:
        # print("===========================================================================================================")
        cur.execute("select * from t_shop_goods")  # 所有商品
        # 使用 fetchone() 方法获取一条数据
        data = cur.fetchall()

        if data:
            for row in data:
                # 商品sku查询
                cur.execute("select * from t_shop_goods_sku where id_goods="+str(row[0]))
                data_sku_item = cur.fetchall()

                if data_sku_item:
                    for item in data_sku_item:
                        name_list.append(row[14]+"*"+str(item[6]))
                        num_list.append(item[11])
                        pass
                else:
                    # 单规格
                    name_list.append(row[14])
                    num_list.append(row[17])
                    pass
                # print(row)


            # 生成报表
            work_book = xlwt.Workbook(encoding='utf-8')
            sheet = work_book.add_sheet('sheet表名')
            sheet.write(0, 0, '商品名称')
            sheet.write(0, 1, '库存数量')

            for index in range(len(name_list)):
                sheet.write(index+1, 0, name_list[index])
                sheet.write(index+1, 1, num_list[index])
            work_book.save('C:\\Users\\Administrator\\Desktop\\商品库存统计.xls')
        conn.commit()
    finally:
        try:
            cur.close()
            conn.close()
        except Exception as e1:
            print('sql执行成功异常：' + str(e1))


if __name__ == '__main__':
    exe_db()